package com.example.service;

import com.example.entity.H5QuestionnaireVarieties;
import com.example.entity.vo.H5QuestionnaireVO;
import com.example.entity.vo.H5QuestionnaireVO2;
import com.example.mapper.H5QuestionnaireMapper;
import com.example.util.QuestionnaireUtils;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.springframework.beans.BeanUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.testng.annotations.Test;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.lang.reflect.Field;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

/**
 * (H5Questionnaire)表服务接口
 *
 * @author makejava
 * @since 2020-10-29 10:56:31
 */
@Service
@Slf4j
public class H5QuestionnaireService {

    @Autowired
    private H5QuestionnaireMapper h5QuestionnaireMapper;
    private static Field[] declaredFields = H5QuestionnaireVO2.class.getDeclaredFields();

    private static Double[] variety = new Double[3];
    private static Integer varietyIndex = 0;
    private static boolean flag = false;

    //导出excel
    public static void export1(String fileName, Workbook workbook) {

        FileOutputStream fileOut = null;
        try {
            String exportFilePath = fileName;
            File exportFile = new File(exportFilePath);
            if (!exportFile.exists()) {
                exportFile.createNewFile();
            }
            fileOut = new FileOutputStream(exportFilePath);
            workbook.write(fileOut);
            fileOut.flush();
        } catch (Exception e) {
            log.warn("输出Excel时发生错误，错误原因：" + e.getMessage());
        } finally {
            try {
                if (null != fileOut) {
                    fileOut.close();
                }
                if (null != workbook) {
                    workbook.close();
                }
            } catch (IOException e) {
                log.warn("关闭输出流时发生错误，错误原因：" + e.getMessage());
            }
        }
    }


    /**
     * 将数据转换成行
     *
     * @param data 源数据
     * @param row  行对象
     * @return
     */
    private static void convertDataToRow(H5QuestionnaireVO2 data, Row row, Workbook workbook) {
        // font
        Font font = workbook.createFont();
        font.setColor(Font.COLOR_RED);
        // style
        CellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setFont(font);

        int cellNum = 0;
        Cell cell;

        // 第0列 用户id
        cell = row.createCell(cellNum++);
        if (data.getUserId() != null) {
            cell.setCellValue(data.getUserId());
        }

        // 第1-119列数据 利用反射
        Map<String, Integer> titleMap = QuestionnaireUtils.getTitleMap();
        for (Map.Entry<String, Integer> title : titleMap.entrySet()) {
            //log.info("第"+row.getRowNum()+"遍历");

            //通过 当前列 获取对应的对象的 属性值
            Field field = declaredFields[title.getValue() + 2];
            field.setAccessible(true);

            try {
                Object fieldValue = field.get(data);
                cell = row.createCell(cellNum);
                if (fieldValue != null) {
                    log.info("第" + row.getRowNum() + "行，第" + cellNum + "列，内容：" + fieldValue);

                    //处理问题
                    String question = title.getKey();
                    String substring = question.substring(question.lastIndexOf("？") + 1);

                    //获取答案
                    String content = getContent(title.getValue(), substring, fieldValue.toString(), data);


                    cell.setCellValue(content);
                    cell.setCellStyle(cellStyle);

                }

            } catch (IllegalAccessException e) {
                e.printStackTrace();
            }
            cellNum++;
        }


        //120列往后数据
        Map<String, String> userTable = QuestionnaireUtils.getUserTable();
        for (Map.Entry<String, String> title : userTable.entrySet()) {
            try {
                Field field = H5QuestionnaireVO2.class.getDeclaredField(title.getValue());
                field.setAccessible(true);
                Object o = field.get(data);
                //log.info("对象值 = {}", o);
                cell = row.createCell(cellNum);
                if (o != null) {
                    log.info("第" + row.getRowNum() + "行，第" + cellNum + "列，内容：" + o.toString());
                    if (title.getValue().equals("createtime")) {
                        cell.setCellValue(((LocalDateTime) o).format(DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss")));
                    } else {
                        cell.setCellValue(o.toString());
                    }
                }

            } catch (NoSuchFieldException e) {
                e.printStackTrace();
            } catch (IllegalAccessException e) {
                e.printStackTrace();
            }
            cellNum++;
        }

    }

    public static String getContent(int index, String question, String answer, H5QuestionnaireVO2 data) {

        String content = null;
        if (index == 1 || index == 7 || index == 17) { //是否题
            if (answer.equals("1")) {
                content = "是";
            } else if (answer.equals("0")) {
                content = "否";
            }
        } else if (index == 2 || index == 3 || index == 5 || index == 9) { //数字题
            content = answer;
        } else if (index == 4 || index == 11 || index == 12 || index == 13 || index == 16 || index == 18 || index == 21) { //问号+内容
            // TODO 答案中不以其他开头
            if (!question.startsWith("其他") && answer.contains(question)) {
                content = "√";
            } else if (question.startsWith("其他内容") && answer.contains("其他")) {
                String others = answer.substring(answer.indexOf("其他"));
                if (others.contains("@#$")) {
                    others = others.substring(0, others.indexOf("@#$"));
                }
                if (others.length() > 3) {
                    content = others.substring(others.indexOf(":") + 1);
                }
            } else if (question.startsWith("其他") && answer.contains("其他")) {
                content = "其他";
            }
        } else if (index == 6 || index == 8 || index == 10) { //二选一 空，其他
            // TODO
            if (question.equals("") && !answer.startsWith("其他")) {
                content = answer;
            } else if (question.equals("") && answer.startsWith("其他")) {
                content = "其他";
            } else if (question.startsWith("其他内容") && answer.startsWith("其他")) {
                String others = answer.substring(answer.indexOf("其他"));
                if (others.length() > 3) {
                    content = others.substring(others.indexOf(":") + 1);
                }
            }
        } else if (index == 19) {
            // TODO
            String reg = "[^\u4e00-\u9fa5]";
            question = question.replaceAll(reg, ""); //比如 土豆
            //log.info("19题 问题 = {}", question);

            if (question.startsWith("其他内容") && answer.contains("其他")) {
                String others = answer.substring(answer.indexOf("其他"));
                if (others.length() > 3) {
                    content = others.substring(others.indexOf(":") + 1);
                }
            } else if (question.startsWith("其他") && answer.contains("其他")) {
                content = "其他";
            } else if (answer.contains(question) && !question.equals("")) {  //比如 土豆@#$其他:cacao
                content = question;

                List<H5QuestionnaireVarieties> varietiesList = data.getVarietiesList();
                if (varietiesList.size() > 0) {
                    for (H5QuestionnaireVarieties varieties : varietiesList) {

                        if (varieties.getVariety().equals(question)) {
                            variety[0] = varieties.getPremium();
                            variety[1] = varieties.getCompensation();
                            variety[2] = varieties.getEffect();
                            varietyIndex = 0;
                            flag = true;
                            break;
                        }
                    }
                }

            } else {
                if (flag) {
                    Double aDouble = variety[varietyIndex];
                    if (aDouble != null) {
                        content = variety[varietyIndex].toString();
                        varietyIndex++;
                    }
                    if (varietyIndex == 3) {
                        flag = false;
                    }
                }
            }

        } else if (index == 14 || index == 15 || index == 20) {//二选一 答，其他
            if (question.startsWith("其他内容") && answer.contains("其他")) {
                String others = answer.substring(answer.indexOf("其他"));
                if (others.length() > 3) {
                    content = others.substring(others.indexOf(":") + 1);
                }
            } else if (!question.startsWith("其他") && answer.contains("其他")) {
                content = "其他";
            } else if (!question.startsWith("其他内容")) {
                content = answer;
            }
        } else if (index == 22) {
            if (question.endsWith("其他内容") && answer.contains("其他")) {
                String others = answer.substring(answer.indexOf("其他"));
                if (others.contains("@#$")) {
                    others = others.substring(0, others.indexOf("@#$"));
                }
                if (others.length() > 3) {
                    content = others.substring(others.indexOf(":") + 1);
                }
            } else if (question.endsWith("其他") && answer.contains("其他")) {
                content = "其他";
            } else if (!question.contains("其他")) {
                question = question.substring(question.indexOf(" ") + 1);
                if (answer.contains(question)) {
                    int i = answer.indexOf(question);
                    int y = answer.indexOf("@#$", i);
                    y = y == -1 ? answer.length() : y;
                    content = answer.substring(i, y);
                }
            }
        }

        return content;

    }

    /**
     * 设置第一行列头的样式
     *
     * @param workbook 工作簿对象
     * @return 单元格样式对象
     */
    private static CellStyle buildHeadCellStyle(Workbook workbook) {
        CellStyle style = workbook.createCellStyle();
        //对齐方式设置
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setVerticalAlignment(VerticalAlignment.TOP);
        //边框颜色和宽度设置
        style.setBorderBottom(BorderStyle.THIN);
        style.setBottomBorderColor(IndexedColors.BLACK.getIndex()); // 下边框
        style.setBorderLeft(BorderStyle.THIN);
        style.setLeftBorderColor(IndexedColors.BLACK.getIndex()); // 左边框
        style.setBorderRight(BorderStyle.THIN);
        style.setRightBorderColor(IndexedColors.BLACK.getIndex()); // 右边框
        style.setBorderTop(BorderStyle.THIN);
        style.setTopBorderColor(IndexedColors.BLACK.getIndex()); // 上边框
        //设置背景颜色
        style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
        style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        //粗体字设置
        Font font = workbook.createFont();
        font.setBold(true);
        style.setFont(font);
        return style;
    }

    //导出 workwoob
    public Workbook getWorkbook() {

        log.info("开始创建工作簿...");

        // 创建工作簿
        Workbook workbook = new SXSSFWorkbook(); //生成.xlsx的excel
        // 创建工作表
        SXSSFSheet sheet = (SXSSFSheet) workbook.createSheet();

        // 构建头单元格样式
        CellStyle cellStyle = buildHeadCellStyle(sheet.getWorkbook());
        cellStyle.setWrapText(true);//自动换行

        log.info("开始创建第1行...");
        Row head = sheet.createRow(1);//列是从0开始计算的，我这里空了一行
        head.setHeight((short) (8 * 20 * 20)); //1/20th of a point

        //第0列标题,用户id
        Cell cellUid = head.createCell(0);
        cellUid.setCellValue("UserID");

        //第1-119列标题
        Map<String, Integer> titleMap = QuestionnaireUtils.getTitleMap();
        int i = 1;
        for (Map.Entry<String, Integer> titie : titleMap.entrySet()) {
            Cell cell = head.createCell(i);
            cell.setCellValue(titie.getKey());
            cell.setCellStyle(cellStyle);
            sheet.setColumnWidth(i, 5 * 2 * 256); //in units of 1/256th of a character width
            i++;
        }

        //第120列往后标题
        Map<String, String> userTable = QuestionnaireUtils.getUserTable();
        for (Map.Entry<String, String> title : userTable.entrySet()) {
            Cell cell = head.createCell(i);
            cell.setCellValue(title.getKey());
            cell.setCellStyle(cellStyle);
            sheet.setColumnWidth(i, 10 * 2 * 256); //in units of 1/256th of a character width
            i++;
        }


        log.info("开始处理数据...");
        List<H5QuestionnaireVO> list = h5QuestionnaireMapper.selectQuestionnaire();
        int rowNum = 2;

        for (Iterator<H5QuestionnaireVO> it = list.iterator(); it.hasNext(); ) {
            H5QuestionnaireVO data = it.next();
            if (data == null) {
                continue;
            }
            // 构建每行的数据内容
            Row row = sheet.createRow(rowNum++);

            H5QuestionnaireVO2 h5QuestionnaireVO2 = new H5QuestionnaireVO2();
            BeanUtils.copyProperties(data, h5QuestionnaireVO2);
            convertDataToRow(h5QuestionnaireVO2, row, workbook);
        }


        log.info("导出完成");
        return workbook;
    }

    @Test
    public void write() {
        String fileName = "C:\\Users\\Administrator\\Desktop\\out.xlsx"; //输出文件
        String fileType = fileName.substring(fileName.lastIndexOf(".") + 1); //文件后缀

        log.info("开始创建工作簿...");

        // 创建工作簿
        Workbook workbook = null;
        if (fileType.equals("xls")) {
            workbook = new HSSFWorkbook(); //生成.xls的excel
        } else if (fileType.equals("xlsx")) {
            workbook = new SXSSFWorkbook(); //生成.xlsx的excel
        } else {
            log.error("文件格式错误");
            return;
        }


        // 创建工作表
        Sheet sheet = workbook.createSheet();
        // 构建头单元格样式
        CellStyle cellStyle = buildHeadCellStyle(sheet.getWorkbook());
        cellStyle.setWrapText(true);//自动换行


        log.info("开始创建第1行...");
        Row head = sheet.createRow(1);//列是从0开始计算的，我这里空了一行
        head.setHeight((short) (8 * 20 * 20)); //1/20th of a point

        //第0列标题,用户id
        Cell cellUid = head.createCell(0);
        cellUid.setCellValue("UserID");

        //第1-119列标题
        Map<String, Integer> titleMap = QuestionnaireUtils.getTitleMap();
        int i = 1;
        for (Map.Entry<String, Integer> titie : titleMap.entrySet()) {
            Cell cell = head.createCell(i);
            cell.setCellValue(titie.getKey());
            cell.setCellStyle(cellStyle);
            sheet.setColumnWidth(i, 5 * 2 * 256); //in units of 1/256th of a character width
            i++;
        }
        //第120列往后标题
        Map<String, String> userTable = QuestionnaireUtils.getUserTable();
        for (Map.Entry<String, String> title : userTable.entrySet()) {
            Cell cell = head.createCell(i);
            cell.setCellValue(title.getKey());
            cell.setCellStyle(cellStyle);
            sheet.setColumnWidth(i, 5 * 2 * 256); //in units of 1/256th of a character width
            i++;
        }

        log.info("开始处理数据...");
        List<H5QuestionnaireVO> list = h5QuestionnaireMapper.selectQuestionnaire();
        int rowNum = 2;

        for (Iterator<H5QuestionnaireVO> it = list.iterator(); it.hasNext(); ) {
            H5QuestionnaireVO data = it.next();
            if (data == null) {
                continue;
            }
            // 构建每行的数据内容
            Row row = sheet.createRow(rowNum++);

            H5QuestionnaireVO2 h5QuestionnaireVO2 = new H5QuestionnaireVO2();
            BeanUtils.copyProperties(data, h5QuestionnaireVO2);
            convertDataToRow(h5QuestionnaireVO2, row, workbook);
        }

        // 导出
        export1(fileName, workbook);
        log.info("导出完成");
    }


}